﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;

namespace BLL
{
    public class HangHoaBUO:IBUO<tbl_Hang>
    {
        CSDLBanHangDataContext context = new CSDLBanHangDataContext();

        public bool Insert(tbl_Hang obj)
        {
            try
            {
                context.tbl_Hangs.InsertOnSubmit(obj);
                context.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// chen vao bang NCC_Hang
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool InsertNCC_Hang(tbl_NCC_Hang obj)
        {
            try
            {
                context.tbl_NCC_Hangs.InsertOnSubmit(obj);
                context.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// lay id hang vua moi nhap vao
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public String getNewIDHang()
        {
            return context.tbl_Hangs.OrderByDescending(n => n.id_Hang).Select(p => p.id_Hang).FirstOrDefault();
        }

        /// <summary>
        /// lay id nha cung cap theo ten nha cung cap
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public String getIDNCC(String nameNCC)
        {
            return context.tbl_NhaCungCaps.Where(p => p.TenNhaCungCap.Trim().Equals(nameNCC.Trim())).Select(n => n.id_NhaCungCap).FirstOrDefault();
        }

        /// <summary>
        /// lay id cua NCC co ten nameNCC gan vao tblNCC_Hang khi cap nhat
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public String getIDNCC_Hang(String nameNCC)
        {
            return context.tbl_NhaCungCaps.Where(p => p.TenNhaCungCap.Equals(nameNCC)).Select(n => n.id_NhaCungCap).FirstOrDefault();
        }

        /// <summary>
        /// chen id_NCC vao tbl_NCC_Hang
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool UpdateNCC_Hang(String id, tbl_NCC_Hang obj)
        {
                tbl_NCC_Hang nccH = context.tbl_NCC_Hangs.Where(p => p.id_Hang.Equals(id)).FirstOrDefault();
                nccH.id_NhaCungCap = obj.id_NhaCungCap;

                context.SubmitChanges();
                return true;
        }

        public bool Delete(String id)
        {
            try
            {
                tbl_Hang obj = context.tbl_Hangs.Where(p => p.id_Hang.Equals(id)).FirstOrDefault();
                context.tbl_Hangs.DeleteOnSubmit(obj);
                context.SubmitChanges();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool Delete(tbl_Hang obj)
        {
            throw new NotImplementedException();
        }

        public List<tbl_Hang> GetAll()
        {
            return context.tbl_Hangs.OrderByDescending(p => p.id_Hang).ToList<tbl_Hang>();
        }

        public IQueryable HienThiHangHoa()
        {
            var list = context.tbl_Hangs.Join(
                    context.tbl_NCC_Hangs,
                    a => a.id_Hang,
                    b => b.id_Hang,
                    (a, b) => new
                    {
                        id_Hang = a.id_Hang,
                        TenHang = a.TenHang,
                        NSX = a.NSX,
                        DonGia = a.DonGia,
                        id_NhaCungCap = b.id_NhaCungCap
                    }
                ).Join(
                    context.tbl_NhaCungCaps,
                    c => c.id_NhaCungCap,
                    d => d.id_NhaCungCap,
                    (c, d) => new 
                    {
                        id_NhaCungCap = c.id_NhaCungCap,
                        id_Hang = c.id_Hang,
                        TenNhaCungCap = d.TenNhaCungCap,
                        TenHang = c.TenHang,
                        NSX = c.NSX,
                        DonGia = c.DonGia
                    }
                ).OrderByDescending(p => p.id_Hang);
            return list;
        }
        /// <summary>
        /// Lấy 1 dòng trong cơ sở dữ liệu theo id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public tbl_Hang GetSingle(String id)
        {
            return context.tbl_Hangs.Where(p => p.id_Hang.Trim().Equals(id.Trim())).FirstOrDefault();
        }

        public bool Update(String id, tbl_Hang obj)
        {
            tbl_Hang ha = context.tbl_Hangs.Where(p => p.id_Hang == id.Trim()).FirstOrDefault();
            ha.TenHang = obj.TenHang;
            ha.NSX = obj.NSX;
            ha.DonGia = obj.DonGia;

            context.SubmitChanges();
            return true;
        }
        /// <summary>
        /// Tìm kiếm theo tên hàng hóa
        /// </summary>
        /// <param name="tenhang"></param>
        /// <returns></returns>
        public List<tbl_Hang> searchByTenHang(String tenhang)
        {
            List<tbl_Hang> list = new List<tbl_Hang>();
            List<tbl_Hang> all = GetAll();

            if (all.Count > 0)
            {
                foreach (tbl_Hang a in all)
                {
                    if (a.TenHang.ToLower().Contains(tenhang.ToLower()))
                    {
                        list.Add(a);
                    }
                }
            }
            return list;
        }

        public string[] SearchIDHang(String item)
        {
            return context.tbl_Hangs.Where(n => n.id_Hang.Contains(item.Trim())).Select(p => p.id_Hang).ToArray();
        }
        /// <summary>
        /// Tìm kiếm theo mã hàng hóa
        /// </summary>
        /// <param name="mahang"></param>
        /// <returns></returns>
        public List<tbl_Hang> searchByMaHang(String mahang)
        {
            return context.tbl_Hangs.Where(p => p.id_Hang.ToLower().Trim().Contains(mahang.ToLower().Trim())).ToList<tbl_Hang>();
        }
        /// <summary>
        /// Tìm kiếm theo ngày sản xuất
        /// </summary>
        /// <param name="ngaySX"></param>
        /// <returns></returns>
        public List<tbl_Hang> searchByNgaySX(DateTime ngaySX)
        {
            DateTime dt = (DateTime)ngaySX;
            return context.tbl_Hangs.Where(p => DateTime.Compare((DateTime)p.NSX, dt) == 0).ToList<tbl_Hang>();
        }

        public List<tbl_Hang> searchByKhoangGia0(int giatu, int giaden)
        {
            var query = context.tbl_Hangs.Where(p => p.DonGia > giatu && p.DonGia < giaden).OrderBy(n => n.NSX).ToList();
            return query;
        }

        public List<tbl_Hang> searchByKhoangGia1(int giatu, int giaden)
        {
            var query = context.tbl_Hangs.Where(p => p.DonGia > giatu && p.DonGia < giaden).OrderBy(n => n.DonGia).ToList();
            return query;
        }

        public List<tbl_Hang> searchByKhoangGia2(int giatu, int giaden)
        {
            var query = context.tbl_Hangs.Where(p => p.DonGia > giatu && p.DonGia < giaden).OrderByDescending(n => n.DonGia).ToList();
            return query;
        }

        public String GetIDHangByTenHang(String tenhang)
        {
            return context.tbl_Hangs.Where(p => p.TenHang.Equals(tenhang)).Select(n => n.id_Hang).FirstOrDefault();
        }

        public IQueryable getHangHoaByIDNCC(String idNCC)
        {
            var list = context.tbl_NhaCungCaps.Join(
                    context.tbl_NCC_Hangs,
                    a => a.id_NhaCungCap,
                    b => b.id_NhaCungCap,
                    (a, b) => new
                    {
                        id_Hang = b.id_Hang,
                        id_NhaCungCap = a.id_NhaCungCap,
                    }
                ).Join(
                    context.tbl_Hangs,
                    c => c.id_Hang,
                    d => d.id_Hang,
                    (c, d) => new
                    {
                        id_Hang=c.id_Hang,
                        id_NhaCungCap=c.id_NhaCungCap,
                        TenHang=d.TenHang
                    }
                ).Where(p=>p.id_NhaCungCap.Trim().Equals(idNCC.Trim()));
            return list;
        }
    }
}
